package com.just.spider.utils;

import jxl.Workbook;
import jxl.format.*;
import jxl.write.*;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 导出Excel表格的方法
 */
public class ExcelUtil {
    private final static SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");

    /**
     * 从网页导出
     * @param fileName 文件名
     * @param columnList 表头列名
     * @param fieldNameList 对应的参数名
     * @param listContent 内容列表
     * @param maxSheetSize 一张表的最大列数
     * @param response HttpServletResponse
     */
    public static void excelOut(String fileName, List columnList,List fieldNameList,
                                List<? extends Object> listContent,int maxSheetSize, HttpServletResponse response){
        WritableWorkbook book=null;
        OutputStream os=null;
        if(CollectionUtils.isEmpty(listContent)){
           System.out.println("无数据");
        }
        try {
            //获取输出流
            os=response.getOutputStream();
            //清空输出流
            response.reset();
            //设定输出文件头
            response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("GB2312"),"8859_1")+".xls");
            //定义输出类型
            response.setContentType("application/msexcel");
            //建立excel文件
            book= Workbook.createWorkbook(os);
            //判断工作表的个数
            double sheetNum=Math.ceil(listContent.size()/new Integer(maxSheetSize).doubleValue());
            for(int i=0;i<sheetNum;i++){
                if(sheetNum==1) {
                    //创建一个工作表
                    WritableSheet sheet=book.createSheet("sheet0",0);
                    createSheet(sheet,columnList,fieldNameList,listContent,0,listContent.size());
                }else{
                    WritableSheet sheet=book.createSheet("sheet"+i,i);
                    int firstIndex=i*maxSheetSize;
                    int lastIndex=(i+1)*maxSheetSize-1>listContent.size()?listContent.size()-1:(i+1)*maxSheetSize-1;
                    createSheet(sheet,columnList,fieldNameList,listContent,firstIndex,lastIndex);
                }
            }
            //写入
            book.write();
            book.close();
            System.out.println("导出成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出到磁盘
     * @param fileName 文件名
     * @param columnList 表头列名
     * @param fieldNameList 对应的参数名
     * @param listContent 内容列表
     * @param maxSheetSize 一张表的最大列数
     * @param filePath 文件路径
     */
    public static void excelOut(String fileName,String filePath, List columnList,List fieldNameList,
                                List<? extends Object> listContent,int maxSheetSize){
        WritableWorkbook book=null;
        OutputStream os=null;
        if(CollectionUtils.isEmpty(listContent)){
            System.out.println("无数据");
        }
        try {
            //建立excel文件
            book= Workbook.createWorkbook(new File(filePath+fileName));
            //判断工作表的个数
            double sheetNum=Math.ceil(listContent.size()/new Integer(maxSheetSize).doubleValue());
            for(int i=0;i<sheetNum;i++){
                if(sheetNum==1) {
                    //创建一个工作表
                    WritableSheet sheet=book.createSheet("sheet0",0);
                    createSheet(sheet,columnList,fieldNameList,listContent,0,listContent.size());
                }else{
                    WritableSheet sheet=book.createSheet("sheet"+i,i);
                    int firstIndex=i*maxSheetSize;
                    int lastIndex=(i+1)*maxSheetSize-1>listContent.size()?listContent.size()-1:(i+1)*maxSheetSize-1;
                    createSheet(sheet,columnList,fieldNameList,listContent,firstIndex,lastIndex);
                }
            }
            //写入
            book.write();
            book.close();
            System.out.println("导出成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void createSheet(WritableSheet sheet,List columnList,List fieldNameList,List<? extends Object> listContent,int firstIndex,int lastIndex)throws Exception{
        //给sheet电子版中所有的列设置默认的列的宽度;
         sheet.getSettings().setDefaultColumnWidth(20);
        //设置表头信息
        outExcelHeaders(columnList,sheet);
        //设置处理过的内容
        formatMethod(listContent,fieldNameList,sheet,firstIndex,lastIndex);
    }

    /**
     * 设置表头信息，利用jxl
     * @param headersInfo 表头信息列表
     * @param sheet 表
     * @throws WriteException
     */
    public static void outExcelHeaders(List headersInfo,WritableSheet sheet) throws WriteException {
        //设置字体样式
        WritableFont font=new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat cellFormat=new WritableCellFormat(font);
        cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
        for(int i=0;i<headersInfo.size();i++){
            Label label=new Label(i,0,headersInfo.get(i).toString(),cellFormat);
            sheet.addCell(label);

        }
    }

    /**
     * 格式处理，表单内容根据需求变化
     * @param listContent
     * @param fieldNameList
     * @param sheet
     * @throws Exception
     */
    public static void formatMethod(List<? extends Object> listContent,List fieldNameList,WritableSheet sheet,int firstIndex,int lastIndex) throws Exception {
        int i=1;
        for(Object obj:listContent){
            int j = 0;
            String fieldNameTemp = null;
            Object value = null;
            Method method=null;
            Class methodTypeClass=null;
            //sheet.addCell(new Label(j, i, String.valueOf(i)));
            //j++;
            for (Object fieldName : fieldNameList) {
                fieldNameTemp = "get" + fieldName.toString().substring(0, 1).toUpperCase()
                        + fieldName.toString().substring(1);
                method = obj.getClass().getMethod(fieldNameTemp, null);
                methodTypeClass = method.getReturnType();
                //时间格式转换
                if (Date.class.getSimpleName().equals(methodTypeClass.getSimpleName())) {
                    value = dateFormat.format((Date)method.invoke(obj, null));
                }else{
                    value = method.invoke(obj, null);
                }
                if (value == null) {
                    value = "";
                }
                sheet.addCell(new Label(j, i, value.toString()));
                j++;
            }
            i++;
        }
    }
}
